﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Xml;
using SdmxMl.Common;
using SdmxMl.Structure;
using SdmxMl.Manager;
using SdmxMl.SmsImportCfg;
using SdmxMl.Helper;

namespace GeoPlugIn
{
    /// <summary>
    /// Class for generation of SDMX Codelists and hierarchies from State & Territories source document
    /// </summary>
    public class SdmxStateTerritory
    {
        #region Enumeration of generation domains
 
        enum genereType
        {
            History,
            SnapShotGeo,
            SnapShotState
        }
        #endregion

        #region Internal Members

        private DataTable table;
        private WorkSheetHelper wsh;

        private string versionSdmx;
        private string dateValid;

        private List<ArtefactRefTyped> codelistRefList;
        private TextProvider msgProvider;

        private DateTime snapshotDate;

        // Column index from 1
        int colContinent = 12;
        int colregion = 13;
        int colStateFlag = 14;
        int colValid = 23;

        #endregion

        #region Constructor

        public SdmxStateTerritory(IReader ir, string version, TextProvider prov)
        {
            msgProvider = prov;
            ExcelSTreader reader = ir as ExcelSTreader;
            if (reader == null)
                throw new ApplicationException("Invalid reader received for States and Territories.");

            table = reader.Spreadsheet.WorkbookData.Tables[reader.NameStateTerritory];
            wsh = new WorkSheetHelper(table);

            versionSdmx = version;
            dateValid = reader.VersionDate;
            snapshotDate = DateTime.Parse(reader.VersionDate);

            codelistRefList = GetCodelistRef();
        }

        #endregion

        #region Codelist

        public void CreateCodeList(string folder)
        {
            // Pseudo historised codelist
            CodeListType clt = new CodeListType();
            clt.AgencyId = codelistRefList[2].AgencyId;
            clt.Id = codelistRefList[2].Id;
            //clt.ValidFrom = dateValid;

            //clt.Name = msgProvider.GetInternationalizedTextOf("COUNTRY_GEO");
            foreach (string lg in LangSupport.SupportedLanguages)
                clt.Name.UpdateForLanguage(lg, msgProvider.GetTextOf(lg, "COUNTRY_GEO") + " (" + dateValid + ")");

            CreateCodes(clt.CodeList, genereType.History);

            SmManager sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.CodeLists, clt);

            // Put continents and regions if available in regidtry
            ArtefactRefTypedList artl = new ArtefactRefTypedList();
            if (SdmxMl.Multiplexor.Mux.Instance.ExistArtefact(SdmxArtefactType.CodeLists.ToString(), codelistRefList[0].FullIdent))
                artl.Add(new ArtefactRefTyped(SdmxArtefactType.CodeLists, codelistRefList[0].FullIdent));
            if (SdmxMl.Multiplexor.Mux.Instance.ExistArtefact(SdmxArtefactType.CodeLists.ToString(), codelistRefList[1].FullIdent))
                artl.Add(new ArtefactRefTyped(SdmxArtefactType.CodeLists, codelistRefList[1].FullIdent));
            if (artl.Count > 0)
                sm.LoadMissingArtefactsFromRegistry(artl);

            XmlDocument xDoc = sm.GetAsDocument();
            if (string.IsNullOrEmpty(folder) == false)
            {
                string fname = folder + "\\" + clt.FullIdent + ".xml";
                xDoc.Save(fname);
            }
            ReferenceManager.TreeMgr.LoadSdmxArtefact(null, true, xDoc);

      }

        private void CreateCodes(CodeList cl, genereType genType)
        {
            int[] arrAbbrevCol = { 8, 5, 6, 7 };
            int[] arrNameCol = { 0, 9, 10, 11 };
            int[] arrNoteCol = { 0, 20, 21, 22 };

            int row = 2; // Skip header
            cl.HasHierarchy = false;

            string id = string.Empty;
            while (row <= table.Rows.Count && (id = wsh.GetCellContent(row, 1)) != string.Empty)
            {

                bool entryValid = wsh.GetCellContent(row, colValid).Trim().ToLower() == "j";
                bool stateValid = wsh.GetCellContent(row, colStateFlag).Trim().ToLower() == "j";

                if (genType == genereType.History ||
                    (genType == genereType.SnapShotGeo && entryValid) ||
                    (genType == genereType.SnapShotState && entryValid && stateValid))
                {
                    // Code Value
                    CodeType c = new CodeType();
                    c.Id = id;

                    // SDMX Name internationalized (from SG_NAMK)
                    wsh.GetMultilang(c.Name, row, arrAbbrevCol);

                    // Long Name SG_NAME (no english)
                    Annotation ann = new Annotation("SG_NAME");
                    wsh.GetMultilang(ann.Text, row, arrNameCol);

                    if (c.Name.IsEmpty)
                    {
                        foreach (string lg in LangSupport.SupportedLanguages)
                            c.Name.UpdateForLanguage(lg, ann.Text.GetForLanguage(lg));
                    }
                    else
                    {
                        if (genType == genereType.History && ann.Text.IsEmpty == false)
                            c.AnnotationList.Add(ann);
                    }

                    if (genType == genereType.History)
                    {
                        // Note Internationalized
                        ann = new Annotation("NOTE");
                        wsh.GetMultilang(ann.Text, row, arrNoteCol);
                        if (ann.Text.IsEmpty == false)
                            c.AnnotationList.Add(ann);

                        // Notes
                        AddAnnotation(row, c, 2, "SG_UNOC");
                        AddAnnotation(row, c, 3, "SG_ISO2");
                        AddAnnotation(row, c, 4, "SG_ISO3");

                        AddAnnotation(row, c, 12, "SG_KONT");
                        AddAnnotation(row, c, 13, "SG_REGI");

                        AddAnnotation(row, c, 14, "SG_STAAT");
                        AddAnnotation(row, c, 15, "SG_STAG");
                        AddAnnotation(row, c, 16, "SG_UNOMI");
                        AddAnnotation(row, c, 17, "SG_UNOMD");
                        AddAnnotation(row, c, 18, "SG_CHSA");
                        AddAnnotation(row, c, 19, "SG_CHSD");
                        AddAnnotation(row, c, 23, "SG_VAL");
                        AddAnnotation(row, c, 24, "SG_MUTDAT");
                    }

                    cl.Add(c);
                }
                ++row;
            }
        }

        private void AddAnnotation(int row, CodeType c, int col, string tag)
        {
            Annotation ann = new Annotation(tag);
            ann.Text.UpdateDefaultText(wsh.GetCellContent(row, col));
            if (ann.Text.IsEmpty == false)
                c.AnnotationList.Add(ann);
        }
        #endregion

        #region Hierarchy

        public void CreateHierarchy(string folder)
        {
            // Create srtefact
            HierarchicalCodelistType he = new HierarchicalCodelistType();
            he.Id = "HCL_COUNTRIESGEO";
            he.AgencyId = codelistRefList[2].AgencyId;
            //he.ValidFrom = dateValid;

            //Name Internationalized
            foreach (string lg in LangSupport.SupportedLanguages)
                he.Name.UpdateForLanguage(lg, msgProvider.GetTextOf(lg, "HIST_COUNTRY_GEO")  + " (" + dateValid + ")");
            
            // Create references
            int aliasCd = 0;
            foreach (ArtefactRefTyped art in codelistRefList)
            {
                ++aliasCd;
                // Create references
                CodelistRefType che = new CodelistRefType();
                che.AgencyId = art.AgencyId;
                che.Id = art.Id;
                che.Alias = aliasCd.ToString();
                che.Version = art.Version;

                he.CodelistRefList.Add(che);
            }

            // Create Hierarchy container (one for GEO one for POLIT)

            // GEO
            HierarchyType heTypeGeo = new HierarchyType(he.CodelistRefList);
            heTypeGeo.Id = "HR_" + he.Id.Substring(4);
            heTypeGeo.Name.UpdateDefaultText("Hierarchy");
            he.HierarchyList.Clear();
            he.HierarchyList.Add(heTypeGeo);

            // Create levels (Reflects codelistref)
            int order = 0;
            int[]arrSize = { 1, 2, 4 };
            foreach (ArtefactRefTyped art in codelistRefList)
            {
                LevelType level = new LevelType();
                level.Id = "LV_" + art.Id.Substring(3);
                level.Name.UpdateDefaultText(art.Id.Substring(3));
                level.CodingType.TextType = TextTypeType.Integer;
                level.CodingType.maxLength = level.CodingType.minLength = arrSize[order].ToString();
                level.Order = ++order;
                heTypeGeo.LevelList.Add(level);
            }

            // POLIT
            HierarchyType heTypePolit = new HierarchyType(he.CodelistRefList);
            heTypePolit.Id = "HR_COUNTRIESPOLIT";
            heTypePolit.Name.UpdateDefaultText("Hierarchy POLIT");
            he.HierarchyList.Add(heTypePolit);
            order = 0;
            int orderPolit = 0;
            foreach (ArtefactRefTyped art in codelistRefList)
            {
                if (++order == 2)
                    continue;
                LevelType level = new LevelType();
                level.Id = "LV_" + art.Id.Substring(3);
                level.Name.UpdateDefaultText(art.Id.Substring(3));
                level.CodingType.TextType = TextTypeType.Integer;
                level.CodingType.maxLength = level.CodingType.minLength = arrSize[order-1].ToString();
                level.Order = ++orderPolit;
                heTypePolit.LevelList.Add(level);
            }

            
            // Create Hierarchy (check parent existence)
            CreateHierarchyGeo(heTypeGeo);
            CreateHierarchyPolit(heTypePolit, false);

            //Persist
            SmManager sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.HierarchicalCodelists, he);
            XmlDocument xDoc = sm.GetAsDocument();
            if (string.IsNullOrEmpty(folder) == false)
            {
                string fname = folder + "\\" + he.FullIdent + ".xml";
                xDoc.Save(fname);
            }
            ReferenceManager.TreeMgr.LoadSdmxArtefact(null, true, xDoc);

        }

        /// <summary>
        /// Get codelists references fullId's from configuration file
        /// </summary>
        /// <returns>list of artefact ref</returns>
        private List<ArtefactRefTyped> GetCodelistRef()
        {
           List<ArtefactRefTyped> artList = new List<ArtefactRefTyped>();

            string keys = ConfigurationManager.AppSettings["CodeListsRef"];
            if (string.IsNullOrEmpty(keys) == false)
            {
                string[]keyList = keys.Split(';');
                if (keyList.Length != 3)
                    throw new ApplicationException("Invalid CodeLists references count in configuration file!");
                foreach (string fullId in keyList)
                {
                    ArtefactRefTyped art = new ArtefactRefTyped(
                        SdmxArtefactType.CodeLists, fullId);
                    artList.Add(art);
                }
            }
            else
            {
                throw new ApplicationException("CodeLists references are not defined in configuration file!");
            }

            return artList;
        }

        private void CreateHierarchyGeo(HierarchyType hierarchy)
        {
            bool entryValid = false;

            int row = 2; // Skip header
            string id = string.Empty;
            string continentCode = string.Empty;
            string regionCode = string.Empty;

            CodeRefType heRefContinent = null;
            CodeRefType heRefRegion = null;

            while (row <= table.Rows.Count &&
                (id = wsh.GetCellContent(row, 1)) != string.Empty)
            {
                entryValid = wsh.GetCellContent(row, colValid).Trim().ToLower() == "j";

                if (entryValid)
                {
                    continentCode = wsh.GetCellContent(row, colContinent);
                    regionCode = continentCode + wsh.GetCellContent(row, colregion);

                    if (CheckExisting(continentCode, regionCode))
                        throw new ApplicationException(string.Format("Unexpected code for Continent and region.{0}Continent:{1}, Region:{2}",
                            Environment.NewLine, continentCode, regionCode));

                    
                    // Crete associated reference item in hierarchy
                    CodeRefType heRefcode = new CodeRefType();
                    heRefcode.CodeID = id;
                    heRefcode.CodelistAliasRef = "3";
                   // heRefcode.CodelistAliasRef = "2";

                    // Exists Continent
                    if ((heRefContinent = LocateCodeRefInLevel(hierarchy.CodeRefList, continentCode)) == null)
                    {
                        heRefContinent = new CodeRefType();
                        heRefContinent.CodeID = continentCode;
                        heRefContinent.CodelistAliasRef = "1";
                        hierarchy.CodeRefList.Add(heRefContinent);
                    }

                    
                    //Exists Region
                    if ((heRefRegion = LocateCodeRefInLevel(heRefContinent.CodeRefList, regionCode)) == null)
                    {
                        heRefRegion = new CodeRefType();
                        heRefRegion.CodeID = regionCode;
                        heRefRegion.CodelistAliasRef = "2";
                        heRefContinent.CodeRefList.Add(heRefRegion);
                    }

                    // Add 
                    heRefRegion.CodeRefList.Add(heRefcode);
                     
                    // Add 
                   // heRefContinent.CodeRefList.Add(heRefcode);
              
                }

                ++row;

            }
        }

        private void CreateHierarchyPolit(HierarchyType hierarchy, bool snapshot)
        {
            bool entryValid = false;

            int row = 2; // Skip header
            string id = string.Empty;
            string continentCode = string.Empty;
            string regionCode = string.Empty;

            CodeRefType heRefContinent = null;

            while (row <= table.Rows.Count &&
                (id = wsh.GetCellContent(row, 1)) != string.Empty)
            {
                entryValid = wsh.GetCellContent(row, colValid).Trim().ToLower() == "j" &&
                    wsh.GetCellContent(row, colStateFlag).Trim().ToLower() == "j";

                if (entryValid)
                {
                    continentCode = wsh.GetCellContent(row, colContinent);

                    // Crete associated reference item in hierarchy
                    CodeRefType heRefcode = new CodeRefType();
                    heRefcode.CodeID = id;
                    heRefcode.CodelistAliasRef = (snapshot) ? "2" : "3";

                    // Exists Continent
                    if ((heRefContinent = LocateCodeRefInLevel(hierarchy.CodeRefList, continentCode)) == null)
                    {
                        heRefContinent = new CodeRefType();
                        heRefContinent.CodeID = continentCode;
                        heRefContinent.CodelistAliasRef = "1";
                        hierarchy.CodeRefList.Add(heRefContinent);
                    }


                    // Add 
                    heRefContinent.CodeRefList.Add(heRefcode);

                }

                ++row;

            }
        }

        private CodeRefType LocateCodeRefInLevel(CodeRefTypeList crtl, string id)
        {
            CodeRefType ret = null;

            foreach (CodeRefType cr in crtl)
                if (cr.CodeID == id)
                {
                    ret = cr;
                    break;
                }
            return ret;
        }
        

        #endregion

        #region SnapShot

        private void DatedName(MultiLang ml, DateTime snapshotDate)
        {
            if (ml != null)
            {
                foreach (string lg in LangSupport.SupportedLanguages)
                {
                    ml.UpdateForLanguage(lg, ml.GetIfDefinedForLanguage(lg) + " (" +
                        snapshotDate.ToString("dd.MM.yyyy") + ")");
                }
            }
        }

        public void CreateSnapshotGeo(string folder)
        {
            CodeListType clt = new CodeListType();
            clt.AgencyId = codelistRefList[2].AgencyId;
            clt.Id = codelistRefList[2].Id;
            clt.Version = snapshotDate.Year.ToString() + ".1"; ;
            clt.ValidFrom = dateValid;
            clt.Name = msgProvider.GetInternationalizedTextOf("COUNTRY_GEO");
            DatedName(clt.Name, snapshotDate);

            CreateCodes(clt.CodeList, genereType.SnapShotGeo);


            // Create srtefact
            HierarchicalCodelistType he = new HierarchicalCodelistType();
            he.Id = "HCL_COUNTRIESGEO";
            he.AgencyId = codelistRefList[2].AgencyId;
            he.Version = snapshotDate.Year.ToString() + ".1"; ;
            he.ValidFrom = dateValid;

            //Name Internationalized
            he.Name = msgProvider.GetInternationalizedTextOf("COUNTRY_GEO");
            DatedName(he.Name, snapshotDate);

            // Create references
            int aliasCd = 0;
            int orderPolit = 1;
            foreach (ArtefactRefTyped art in codelistRefList)
            {
                ++aliasCd;
          //      if (++aliasCd == 2)
          //          continue;
                // Create references
                CodelistRefType che = new CodelistRefType();
                che.AgencyId = art.AgencyId;
                che.Id = art.Id;
                che.Alias = orderPolit.ToString();
                ++orderPolit;

                che.Version = art.Version;
                if (aliasCd == 3)
                {
                    che.Id = clt.Id;
                    che.Version = clt.Version;
                }

                he.CodelistRefList.Add(che);
            }

            // GEO
            HierarchyType heTypeGeo = new HierarchyType(he.CodelistRefList);
            heTypeGeo.Id = "HR_" + he.Id.Substring(4);
            heTypeGeo.Name.UpdateDefaultText("Hierarchy");
            he.HierarchyList.Clear();
            he.HierarchyList.Add(heTypeGeo);

            // Create levels (Reflects codelistref)
            int order = 0;
            int[]arrSize = { 1, 2, 4 };
            foreach (ArtefactRefTyped art in codelistRefList)
            {
                LevelType level = new LevelType();
                level.Id = "LV_" + art.Id.Substring(3);
                level.Name.UpdateDefaultText(art.Id.Substring(3));
                level.CodingType.TextType = TextTypeType.Integer;
                level.CodingType.maxLength = level.CodingType.minLength = arrSize[order].ToString();
                level.Order = ++order;
                heTypeGeo.LevelList.Add(level);
            }
            
            // Create Hierarchy (check parent existence)
            CreateHierarchyGeo(heTypeGeo);

            //Persist
            SmManager sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.CodeLists, clt);
            sm.PutArtefact(SdmxArtefactType.HierarchicalCodelists, he);
            XmlDocument xDoc = sm.GetAsDocument();
            if (string.IsNullOrEmpty(folder) == false)
                xDoc.Save(folder + "\\" + he.FullIdent + ".xml");


        }

        public void CreateSnapshotStates(string folder)
        {
            CodeListType clt = new CodeListType();
            clt.AgencyId = codelistRefList[2].AgencyId;
            clt.Id = "CL_STATES";
            clt.Version = snapshotDate.Year.ToString() + ".1"; ;
            clt.ValidFrom = dateValid;
            clt.Name = msgProvider.GetInternationalizedTextOf("STATES");
            DatedName(clt.Name, snapshotDate);

            CreateCodes(clt.CodeList, genereType.SnapShotState);

            // Create srtefact
            HierarchicalCodelistType he = new HierarchicalCodelistType();
            he.Id = "HCL_COUNTRIESPOLIT";
            he.AgencyId = codelistRefList[2].AgencyId;
            he.Version = snapshotDate.Year.ToString() + ".1"; ;
            he.ValidFrom = dateValid;

            //Name Internationalized
            he.Name = msgProvider.GetInternationalizedTextOf("STATES");
            DatedName(he.Name, snapshotDate);

            // Create references
            int aliasCd = 0;
            int orderPolit = 1;
            foreach (ArtefactRefTyped art in codelistRefList)
            {
                if (++aliasCd == 2)
                    continue;
                // Create references
                CodelistRefType che = new CodelistRefType();
                che.AgencyId = art.AgencyId;
                che.Id = art.Id;
                che.Alias = orderPolit.ToString();
                ++orderPolit;

                che.Version = art.Version;
                if (aliasCd == 3)
                {
                    che.Id = clt.Id;
                    che.Version = clt.Version;
                }

                he.CodelistRefList.Add(che);
            }

            // POLIT
            he.HierarchyList.Clear();
            HierarchyType heTypePolit = new HierarchyType(he.CodelistRefList);
            heTypePolit.Id = "HR_COUNTRIESPOLIT";
            heTypePolit.Name = msgProvider.GetInternationalizedTextOf("STATES");
            DatedName(heTypePolit.Name, snapshotDate);

            he.HierarchyList.Add(heTypePolit);
            int order = 0;
            orderPolit = 0;
            int[] arrSize = { 1, 2, 4 };
            foreach (ArtefactRefTyped art in codelistRefList)
            {
                if (++order == 2)
                    continue;
                LevelType level = new LevelType();
                level.Id = "LV_" + art.Id.Substring(3);
                level.Name.UpdateDefaultText(art.Id.Substring(3));
                level.CodingType.TextType = TextTypeType.Integer;
                level.CodingType.maxLength = level.CodingType.minLength = arrSize[order - 1].ToString();
                level.Order = ++orderPolit;
                heTypePolit.LevelList.Add(level);
            }


            // Create Hierarchy (check parent existence)
            CreateHierarchyPolit(heTypePolit, true);

            //Persist
            SmManager sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.CodeLists, clt);
            sm.PutArtefact(SdmxArtefactType.HierarchicalCodelists, he);
            XmlDocument xDoc = sm.GetAsDocument();
            if (string.IsNullOrEmpty(folder) == false)
                xDoc.Save(folder + "\\" + he.FullIdent + ".xml");
        }

        #endregion

        #region Checking of existence of continent or region code

        /// <summary>Check valid code for Region and Continent</summary>
        /// <param name="continent"></param>
        /// <param name="region"></param>
        /// <returns>True on error</returns>
        private bool CheckExisting(string continent, string region)
        {
            bool ret = false;

            // Was not required, but who knows in futur !

            return ret;
        }
        #endregion
    }
}
